/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.data.db;

import java.io.OutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import edu.cmu.lti.sepia.data.Lang;
import edu.cmu.lti.sepia.infra.topic.model.Answer;
import edu.cmu.lti.sepia.infra.topic.model.Nugget;
import edu.cmu.lti.sepia.infra.topic.model.Progress;
import edu.cmu.lti.sepia.infra.topic.model.Topic;
import edu.cmu.lti.sepia.util.FormatUtil;
import edu.cmu.lti.sepia.util.ServletUtil;

/**
 * 
 * Manages Topic object in a DB.
 * 
 * @see Topic
 * @author Hideki Shima
 *
 */
public class TopicDAO {

  public static String[] excelColumns = {"Internal ID","External ID","Selected",
    "Title","Answer Type","Question (src)","Question (trg)","Narrative (src)","Narrative (trg)","Note"};
  
	/**
	 * List of topics displayed depending on tool and userId... No answers provided.
	 * @param sql
	 * @param short task name
	 * @param tool
	 * @param userId
	 * @return topics
	 */
	public static List<Topic> getTopics( SQL sql, String table, String tool, int userId ) 
		throws SQLException {
	  
	  if (tool==null) tool="td"; //TD done
		
		String query = Query.GET_TOPICS;
	    
		if ( tool.equals("ne") ) { //NE done
			query += ", @T@.progress p WHERE ";
			query += " p.tool='td' AND p.is_done=1 AND t.topic_id=p.topic_id";
		} else if ( tool.equals("nv") ) {//NV done
			query += ", @T@.progress p WHERE ";
			query += " p.tool='ne' AND p.is_done=1 AND t.topic_id=p.topic_id";//p.user_id=? AND 
		}
		
		query = Query.generate( query, table );
		PreparedStatement ps = sql.getConnection().prepareStatement(query);
		ResultSet rs = null;
		List<Topic> topics = new ArrayList<Topic>();
		//if ( tool.equals("nv") ) ps.setInt(1, userId); 
		rs = sql.executeSelect(ps);
		while ( rs.next() ) {
			Topic t = rsToObject( rs );
			topics.add( t );
		}
		rs.close();
		ps.close();
	
		ProgressDAO.loadProgress( sql, table, tool, topics, userId );
		
		return topics;
	}
	
	/**
	 * Topics with answers
	 * @param sql
	 * @param table
	 * @param userId -- only matters when doneLevel is nv
	 * @param doneLevel one of td, ne, nv
	 * @return topics
	 * @throws SQLException
	 */
	public static List<Topic> getGoldStandard( SQL sql, String table, int userId, String doneLevel ) 
  throws SQLException {
	  List<Topic> topics = getTopics(sql,table,doneLevel,userId);
	  for ( Topic t : topics ) {
	    List<Answer> answers = AnswerDAO.findByTopicId( sql, table, t.getTopicId() );
	    List<Nugget> nuggets = NuggetDAO.findByTopicId( sql, table, t.getTopicId() );
	    t.setAnswers(answers);
	    t.setNuggets(nuggets);
	  }
	  return topics;
	}
	
	public static void addTopic( SQL sql, String table, String title ) throws SQLException  {
		String query = Query.generate( Query.ADD_TOPIC, table );		
		PreparedStatement ps = sql.getConnection().prepareStatement(query);
		ps.setString(1, title);
		sql.execute(ps);
		ps.close();
	}

	public static void addTopic( SQL sql, String table, Topic topic ) throws SQLException  {
	  String query = Query.generate( Query.ADD_TOPIC_FULL, table );		
	  PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ps.setString(1, topic.getExternalId());
    ps.setString(2, topic.getAnswerType());
    ps.setString(3, topic.getQuestionSrc());
    ps.setString(4, topic.getQuestionTrg());
    ps.setString(5, topic.getNarrativeSrc());
    ps.setString(6, topic.getNarrativeTrg());
    ps.setString(7, topic.getNote());
    ps.setString(8, topic.getTitle());
	  sql.execute(ps);
	  ps.close();
	}
	
	public static void updateTopic( SQL sql, String table, Topic topic ) throws SQLException  {
		
		String query = Query.generate( Query.UPDATE_TOPIC, table );

		PreparedStatement ps = sql.getConnection().prepareStatement(query);
		ps.setString(1, topic.getExternalId());
		ps.setString(2, topic.getAnswerType());
		ps.setString(3, topic.getQuestionSrc());
		ps.setString(4, topic.getQuestionTrg());
		ps.setString(5, topic.getNarrativeSrc());
		ps.setString(6, topic.getNarrativeTrg());
		ps.setString(7, topic.getNote());
		ps.setString(8, topic.getTitle());
		ps.setInt   (9, topic.getTopicId());
		sql.execute(ps);
		ps.close();
	}
	
	public static void insertUpdateTopic( SQL sql, String table, List<Topic> topics ) throws SQLException {
	  for ( Topic topic : topics ) {
	    if ( topic.getTopicId()==-1 ) {
	      addTopic( sql, table, topic );
	    } else {
	      updateTopic( sql, table, topic );
	    }
	  }
	}
	
	public static void deleteTopic( SQL sql, String table, int topicId ) throws SQLException {
		//dependency
		ProgressDAO.deleteProgressByTopicId( sql, table, topicId );
		NuggetDAO.deleteNuggetsByTopicId( sql, table, topicId );
		AnswerDAO.deleteAnswersByTopicId( sql, table, topicId );
		
		String query = Query.generate( Query.DELETE_TOPIC, table );
		
		PreparedStatement ps = sql.getConnection().prepareStatement(query);
		ps.setInt(1, topicId);
		sql.execute(ps);
		ps.close();
	}
	
	public static Topic findById( SQL sql, String table, Progress progress ) throws SQLException {
		boolean nv = progress.getTool().equals("nv");
		if ( nv ) {
			return findById( sql, table, progress.getTool(), progress.getTopicId(), progress.getUserId() );
		} else {
			return findById( sql, table, progress.getTool(), progress.getTopicId() );
		}
	}
	
	public static Topic findById( SQL sql, String table, String tool, int topicId ) throws SQLException {
		return findById( sql, table, tool, topicId, 0 );
	}
	
	public static Topic findById( SQL sql, String table, String tool, int topicId, int userId ) throws SQLException  {

    String query = Query.generate(Query.FIND_TOPIC, table);
    PreparedStatement ps = sql.getConnection().prepareStatement(query);
    ResultSet rs = null;
    Topic topic = null;

		ps.setInt(1, topicId);
		rs = sql.executeSelect(ps);
		if ( rs.next() ) {
			topic = rsToObject( rs );
			ProgressDAO.loadProgress( sql, table, tool, topic, userId );
			topic.setAnswers( AnswerDAO.findByTopicId( sql, table, topic.getTopicId() ) );
			topic.setNuggets( NuggetDAO.findByTopicId( sql, table, topic.getTopicId() ) );
		}
		rs.close();
		ps.close();

		return topic;
  }
	
	public static int findInternalIdByExternalId( SQL sql, String table, String externalId ) throws SQLException  {
	  
	  String query = Query.generate(Query.FIND_IID_FROM_EID, table);
	  PreparedStatement ps = sql.getConnection().prepareStatement(query);
	  ResultSet rs = null;
	  int topicId = -1;
	  
	  ps.setString(1, externalId);
	  rs = sql.executeSelect(ps);
	  if ( rs.next() ) {
	    topicId = rs.getInt(1);
	  }
	  rs.close();
	  ps.close();
	  
	  return topicId;
	}
	
	public static void removeSimilarNuggets(List<Topic> topics) {
	  for ( Topic t : topics ) {
	    List<Nugget> nuggets = t.getNuggets();
	    if (nuggets==null || nuggets.size()==0) continue;
      
      List<Nugget> toBeRemoved = new ArrayList<Nugget>(nuggets.size());
      for ( Nugget n : nuggets ) {
        if ( n.isSimilar() ) toBeRemoved.add(n);
      }
      nuggets.removeAll(toBeRemoved);
	  }
	}

	/**
	 * Generate sequential external ID, skipping already assigned ones.
	 * @param topics
	 * @param trgLang
	 * @param digit
	 */
	public static void generateExternalIds( List<Topic> topics, String trgLang, int digit ) {
	  String prefix = getExternalIdPrefix(trgLang);
	  int counter = 1;
	  for ( Topic t : topics ) {
	    String currentEID = t.getExternalId();
	    if (currentEID!=null && currentEID.length()>0) {
	      continue;
	    }
	    String newEID = t.isDone()?prefix+String.format("%0"+digit+"d", counter++):"";
      t.setExternalId(newEID);
	  }
	}
	
	private static String getExternalIdPrefix(String trgLang) {
	  return trgLang+"-";
	}
	
	public static Topic findLatest( SQL sql, String table ) throws SQLException {
		String tool = "td"; // guaranteed
		
		String query = Query.generate( Query.FIND_LATEST_TOPIC, table );
	    
		PreparedStatement ps = sql.getConnection().prepareStatement(query);
		ResultSet rs = null;
		Topic topic = null;
		rs = sql.executeSelect(ps);
		if ( rs.next() ) {
			topic = rsToObject( rs );
			ProgressDAO.loadProgress( sql, table, tool, topic, 0 );
			//practically, no need to load answers and nuggets
		}
		rs.close();
		ps.close();

		return topic;
  }
	
	public static void removeUnselected(List<Topic> topics) {
	  Set<Topic> unselected = new HashSet<Topic>(topics.size());
	  for (Topic t:topics) {
	    if (!t.isDone()) {
	      unselected.add(t);
	    }
	  }
	  topics.removeAll(unselected);
	}
	
	private static Topic rsToObject( ResultSet rs ) throws SQLException {
		Topic topic = new Topic( rs.getInt(1), rs.getString(2), rs.getString(3), 
		    rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7),
				rs.getString(8), rs.getString(9));
		return topic;
	}
	
  public static JSONObject convertTopics( Topic topic ) throws JSONException {
    ArrayList<Topic> topics = new ArrayList<Topic>(1);
    topics.add( topic );
    return convertTopics( topics );
  }
  
  public static JSONObject convertSingleTopic( Topic t ) throws JSONException {

    JSONObject j = new JSONObject();
    j.put("topicId", t.getTopicId());
    j.put("externalId", t.getExternalId());
    j.put("title", t.getTitle());
    j.put("questionSrc", t.getQuestionSrc());
    j.put("questionTrg", t.getQuestionTrg());
    j.put("narrativeSrc", t.getNarrativeSrc());
    j.put("narrativeTrg", t.getNarrativeTrg());
    j.put("type", t.getAnswerType());
    j.put("done", (t.isDone()?1:0));
    j.put("note", t.getNote());
    if (t.getAnswers()!=null && t.getAnswers().size()>0 ) {
      j.put("answerTexts", AnswerDAO.convertAnswers( t.getAnswers() ));
    }
    if (t.getNuggets()!=null && t.getNuggets().size()>0 ) {
      j.put("answerNuggets", NuggetDAO.convertNuggets( t.getNuggets() ));
    }
    return j;
  }
  
  /**
   * Incomplete!
    * task := td|ne|nv
    **/
  public static JSONObject convertTopics( List<Topic> topics ) throws JSONException {
    JSONObject json = new JSONObject();
    JSONArray jsonArray = new JSONArray();
    for ( Topic topic : topics ) {
      jsonArray.put(convertSingleTopic(topic));
    }
    json.put("topics", jsonArray);
    return json;
  }
  
  public static void toQuestionExcel( List<Topic> topics, OutputStream os, String lang ) throws Exception {
    toExcel(topics,os,lang,true);
  }
  
  public static void toGoldStandardExcel( List<Topic> topics, OutputStream os, String lang ) throws Exception {
    toExcel(topics,os,lang,false);
  }
  
  private static void toExcel( List<Topic> topics, OutputStream os, String langString, boolean questionsOnly ) throws Exception {
    
    WorkbookSettings ws = new WorkbookSettings();
    Lang lang = Lang.valueOf(langString.toUpperCase());
    ws.setLocale(lang.getLocale());
    
    WritableWorkbook workbook = Workbook.createWorkbook(os, ws);
    WritableSheet sheet = workbook.createSheet("Sheet1", 0);
    
    // Header
    int N=excelColumns.length;
    for ( int xi=0; xi<N; xi++ ) {
      sheet.addCell( new Label(xi,0,excelColumns[xi]) );
    }
    if (!questionsOnly) {
      sheet.addCell( new Label(N,0,"Answer Texts") );
      sheet.addCell( new Label(N+1,0,"Answer Nuggets") );
    }
    
    // Body
    for (int j=0;j<topics.size();j++){
        Topic t = topics.get(j);
        int x=0;
        int y=j+1;
        sheet.addCell( new Label(x++,y,t.getTopicId()+"") );
        sheet.addCell( new Label(x++,y,t.getExternalId()) );
        sheet.addCell( new Label(x++,y,(t.isDone()?1:0)+"") );
        sheet.addCell( new Label(x++,y,t.getTitle()) );
        sheet.addCell( new Label(x++,y,t.getAnswerType()) );
        sheet.addCell( new Label(x++,y,t.getQuestionSrc()) );
        sheet.addCell( new Label(x++,y,t.getQuestionTrg()) );
        sheet.addCell( new Label(x++,y,t.getNarrativeSrc()) );
        sheet.addCell( new Label(x++,y,t.getNarrativeTrg()) );
        sheet.addCell( new Label(x++,y,t.getNote()) );
        List<Answer> answers = t.getAnswers();
        if(!questionsOnly && answers!=null) {
          StringBuilder concatenated = new StringBuilder();
          for ( int counter = 0; counter<answers.size(); counter++ ) {
            Answer a = answers.get(counter);
            String label = a.getLabel().length()>0?"label="+a.getLabel()+" ":"";
            String offset = a.getBegin()>=0&&a.getEnd()>=0?"begin="+a.getBegin()+" end="+a.getEnd()+" ":"";
            String original = a.getOriginalAnswerBearingText().length()>0?"original="+a.getOriginalAnswerBearingText()+" ":"";
            String text = "text="+a.getAnswerBearingText();
            concatenated.append( "["+label+offset+original+text+"]" );
          }
          sheet.addCell( new Label(x++,y,concatenated.toString()) );
        }
        List<Nugget> nuggets = t.getNuggets();
        if(!questionsOnly && nuggets!=null) {
          StringBuilder concatenated = new StringBuilder();
          for ( int counter = 0; counter<nuggets.size(); counter++ ) {
            Nugget n = nuggets.get(counter);
            concatenated.append( "["+FormatUtil.nf4(n.getWeight())+" "+n.getText()+"]" );
          }
          sheet.addCell( new Label(x++,y,concatenated.toString()) );
        }
    }
        
    workbook.write();
    workbook.close();
  }
  
  public static Topic parseRequest( HttpServletRequest request ) {
    if ( request.getParameter("title")==null ) return null;
    
    ServletUtil s = new ServletUtil( request );
    
    return new Topic( 
        s.readInt(   "topicId", -1),
        s.readString("externalId", ""),
        s.readString("type", ""),
        s.readString("questionSrc", ""),
        s.readString("questionTrg", ""),
        s.readString("narrativeSrc", ""),
        s.readString("narrativeTrg", ""),
        s.readString("note", ""),
        s.readString("title", "")
    );
  }
    
}
